﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
namespace DataBaseAccessLib
{
    /// <summary>
    /// 数据库访问操作类
    /// </summary>
    /// <typeparam name="Connection">数据库连接对象</typeparam>
    /// <typeparam name="Command">命令对象</typeparam>
    /// <typeparam name="DataAdapter">数据适配器</typeparam>
    internal class DBAccess<Connection, Command, DataAdapter> : IDBAccess
        where Connection : IDbConnection, new()
        where Command : IDbCommand, new()
        where DataAdapter : IDbDataAdapter, new()
    {
        #region************* 构造 *************

        /// <summary>
        /// 有参数构造方法

        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public DBAccess(string connectionString)
        {
            this.conn = new Connection();
            this.conn.ConnectionString = connectionString;

            this.ConnectionString = connectionString;
            this.SqlConnectionStr = connectionString;
        }

        #endregion


        #region************* 字段 *************

        /// <summary>
        /// 数据库连接

        /// </summary>
        private Connection conn;

        private string ConnectionString = null;

        private string SqlConnectionStr = null;
        #endregion


        #region************* 方法 *************

        /// <summary>
        /// 打开数据库连接

        /// </summary>
        public void OpenConnection()
        {
            try
            {
                if (this.conn.State != ConnectionState.Open)
                {
                    this.conn.Open();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 关闭数据库连接

        /// </summary>
        public void CloseConnection()
        {
            try
            {
                if (this.conn.State != ConnectionState.Closed)
                {
                    this.conn.Close();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 创建执行命令的实例

        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>命令的实例</returns>
        private Command CreateCommand(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = new Command();

            cmd.CommandText = commndText;
            cmd.CommandType = type;
            cmd.Connection = this.conn;
            cmd.CommandTimeout = 12000;

            if (paraList != null)
            {
                foreach (IDbDataParameter para in paraList)
                {
                    cmd.Parameters.Add(para);
                }
            }

            return cmd;
        }

        /// <summary>
        /// 执行返回影响的行数

        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQuery(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = this.CreateCommand(commndText, type, paraList);

            this.OpenConnection();

            int row = -1;

            IDbTransaction tran = conn.BeginTransaction();

            try
            {
                cmd.Transaction = tran;
                row = cmd.ExecuteNonQuery();

                tran.Commit();
            }
            catch (Exception exp)
            {
                tran.Rollback();
                CFile.WriteTxtFile("发生错误时间："+DateTime.Now.ToString());
                CFile.WriteTxtFile("错误的SQL：" + commndText.ToString());
                CFile.WriteTxtFile("错误的内容：" + exp.ToString());
               // throw exp;
            }
            finally
            {
                this.CloseConnection();
            }

            return row;
        }

        /// <summary>
        /// 执行返回数据记录集

        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>数据记录集</returns>
        public DataSet ExecuteDataSet(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = this.CreateCommand(commndText, type, paraList);

            DataAdapter da = new DataAdapter();

            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
            }
            catch (Exception exp)
            {
                throw exp;
            }


            return ds;
        }

        /// <summary>
        /// 执行返回数据表

        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>数据记录集</returns>
        public DataTable ExecuteDataTable(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = this.CreateCommand(commndText, type, paraList);

            DataAdapter da = new DataAdapter();

            da.SelectCommand = cmd;

            DataTable table = null;
            try
            {   DataSet ds = new DataSet();
                da.Fill(ds);

                if ((ds != null) && (ds.Tables.Count > 0))
                {
                    table = ds.Tables[0];
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }

            return table;
        }
        /// <summary>
        /// 执行返回首行首列
        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>首行首列</returns>
        public object ExecuteScalar(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = this.CreateCommand(commndText, type, paraList);

            this.OpenConnection();

            object obj = null;

            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                this.CloseConnection();
            }

            return obj;
        }

        /// <summary>
        /// 执行返回数据读取器

        /// </summary>
        /// <param name="commndText">命令文本</param>
        /// <param name="type">命令类别</param>
        /// <param name="paraList">命令参数</param>
        /// <returns>数据读取器</returns>
        public IDataReader ExecuteDataRead(string commndText, CommandType type, IDbDataParameter[] paraList)
        {
            Command cmd = this.CreateCommand(commndText, type, paraList);

            this.OpenConnection();

            IDataReader read = null;

            try
            {
                read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                throw exp;
            }

            return read;
        }

        /// <summary>
        /// 连接测试
        /// </summary>
        /// <returns>true: 成功</returns>
        public bool TestDBConnection()
        {
            bool isSucceed = false;
            try
            {
                using (Connection conn = new Connection())
                {
                    conn.ConnectionString = this.ConnectionString;

                    conn.Open();
                }

                isSucceed = true;
            }
            catch (Exception exp)
            {
            }

            return isSucceed;
        }

        /// <summary>
        /// 查询分页的数据

        /// </summary>
        /// <param name="pageBeginIndex">开始索引</param>
        /// <param name="pageSize">每一页显示的行数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="dsTableName">数据表名称</param>
        /// <param name="paraList">命令参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(int pageBeginIndex, int pageSize, CommandType commandType, string commandText, string dsTableName, SqlParameter[] paraList)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            cmd.CommandTimeout = 12000;
            if (paraList != null)
            {
                foreach (SqlParameter para in paraList)
                {
                    cmd.Parameters.Add(para);
                }
            }

            cmd.Connection = new SqlConnection(this.SqlConnectionStr);

            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds, pageBeginIndex, pageSize, dsTableName);
                conn.Close();
                conn.Dispose();
            }
            catch (Exception exp)
            {
                return null;
            }

            cmd.Parameters.Clear();

            return ds;
        }


        /// <summary>
        /// 一次性执行多条语句

        /// </summary>
        /// <param name="sqlComms">语句的集合</param>
        /// <returns>true ：成功</returns>
        public bool ExecuteMultipleCmd(List<string> sqlComms)
        {
            Command cmd = this.CreateCommand("", CommandType.Text, null);
            cmd.CommandTimeout = 12000;
            this.OpenConnection();

            int row = 0;

            IDbTransaction tran = conn.BeginTransaction();

            try
            {
                for (int index = 0; index < sqlComms.Count; index++)
                {
                    cmd.CommandText = sqlComms[index];

                    row = cmd.ExecuteNonQuery();
                }

                tran.Commit();
            }
            catch (Exception exp)
            {
                tran.Rollback();

                throw exp;
            }
            finally
            {
                this.CloseConnection();
            }

            return ((row == sqlComms.Count) ? true : false);
        }

        #endregion


        /// <summary>
        /// 创建参数
        /// </summary>
        /// <typeparam name="T">参数类型(继承 IDbDataParameter)</typeparam>
        /// <param name="names">参数名称的集合</param>
        /// <param name="types">参数类型的集合</param>
        /// <param name="values">参数值的集合</param>
        /// <returns>参数类型的数组</returns>
        private T[] CreateDBParameter<T>(string sign,string[] names, DbType[] types, object[] values)
            where T : IDbDataParameter, new()
        {

            T[] paras = null;

            if ((names.Length == types.Length) && (types.Length == values.Length))
            {
                paras = new T[names.Length];

                for (int index = 0; index < names.Length; index++)
                {
                    string name = names[index];
                    char si = name[0];
                    if ((si == '@') || (si == ':'))
                    {
                        name = name.Substring(1, name.Length - 1);
                    }

                    T para = new T();
                    para.ParameterName = sign + name;
                    para.DbType = types[index];

                    if (null != values[index])
                    {
                        para.Value = values[index];
                    }

                    paras[index] = para;
                }
            }

            return paras;
        }


        /// <summary>
        /// 创建参数
        /// </summary>
        /// <typeparam name="T">参数类型(继承 IDbDataParameter)</typeparam>
        /// <param name="names">参数名称的集合</param>
        /// <param name="types">参数类型的集合</param>
        /// <param name="values">参数值的集合</param>
        /// <returns>参数类型的数组</returns>
        public IDbDataParameter[] CreateDBParameter(DBType type, string[] names, DbType[] types, object[] values)
        {
            IDbDataParameter[] paras = null;
            switch (type)
            {
                case DBType.ACCESS:
                    paras = CreateDBParameter<OleDbParameter>("@",names, types, values);
                    break;

                case DBType.ORACLE:
                    paras = CreateDBParameter<OracleParameter>(":",names, types, values);
                    break;

                case DBType.SQL:
                    paras = CreateDBParameter<SqlParameter>("@",names, types, values);
                    break;

            }

            return paras;
        }
    }

}
